package org.lazycoder.database;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * This database class is not intended to be a powerful database abstraction
 * layer. The intent of it is to be a super simple abstraction layer that gives
 * minimal interaction between the client and the database in the hope that this
 * will result in a higher level of security.
 * 
 * @author Matthew Bayliss
 */
public class Database {
	/**
	 * Creates a database with the following name. This can be called on each
	 * access as it currently just uses get connection with create=true. This
	 * works perfectly for Derby but will not work on other types (so it's bad
	 * practise in a way but I'm not planning on using any other DBs). This
	 * class only supports Derby databases. Please don't connect me to anything
	 * else :( I might cry.
	 * 
	 * @param name
	 *            url for database
	 */
	public static void createDB(String name) {
		try {
			DriverManager.getConnection("jdbc:derby:" + name + ";create=true");
		} catch (SQLException e) {
			System.err.println("Cannot create database.");
		}
	}

	/** Contains the database connection */
	private Connection			database;
	private String				dbname	= "";
	/** Database driver name */
	private final String		driver	= "org.apache.derby.jdbc.EmbeddedDriver";

	private PreparedStatement	ps;
	private ResultSet			rs;
	private Statement			st;

	/**
	 * Create a DB object to interact with a database.
	 * 
	 * @param name
	 *            the name of the database (url)
	 */
	public Database(String name) {
		try {
			Class.forName(driver).newInstance();
			dbname = name;
			database = DriverManager.getConnection("jdbc:derby:" + dbname);
			database.setAutoCommit(true);
		} catch (InstantiationException e) {
			System.err.println("Cannot instantiate the driver.");
			System.exit(1);// TODO Make meaningful exit code
		} catch (IllegalAccessException e) {
			System.err.println("Cannot access database");
			System.exit(1);// TODO Make meaningful exit code
		} catch (ClassNotFoundException e) {
			System.err
					.println("driver appears to be invalid. Is it in my path?");
			System.exit(1);// TODO Make meaningful exit code
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Create a table in the database. Usage Example: <code><pre>
db.createTable("people", new String[] {"pid PRIMARY KEY","name VARCHAR(20)"});
   </pre></code>
	 * 
	 * @param name
	 *            name of table to create
	 * @param colSpec
	 *            SQL describing each element of the table.
	 */
	public void createTable(String name, String colSpec[]) {
		String sql = "CREATE TABLE " + name + "(";
		for (String col : colSpec) {
			sql = sql.concat(col);
			if (colSpec[colSpec.length - 1] != col) {
				sql = sql.concat(",\n");
			}
		}
		sql = sql.concat(")");
		try {
			st = database.createStatement();
			log(sql);
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Grab attachments from the database and return them. This will then return
	 * a Vector of the files which can be iterated through. Note, the files are
	 * in the temp directory so don't leave them there.
	 * 
	 * @param table
	 *            the table to get items from
	 * @param field
	 *            the field to get.
	 * @param where
	 *            SQL where clause.
	 * @return A vector of files.
	 */
	public Vector<File> getAttachments(String table, String field, String where) {
		String sql = "SELECT " + field + " FROM " + table + " WHERE " + where;
		log(sql);
		Vector<File> output = new Vector<File>();
		try {
			st = database.createStatement();
			log(sql);
			rs = st.executeQuery(sql);
			while (rs.next()) {
				File file = new File(System.getProperty("java.io.tmpdir")
						+ "/DBOut-" + where + ".png");
				file.createNewFile();
				InputStream ds = rs.getBinaryStream(field);
				FileOutputStream fs = new FileOutputStream(file);
				byte[] buffer = new byte[1];
				while (ds.read(buffer) > 0) {
					fs.write(buffer);
				}
				output.add(file);
				fs.flush();
				fs.close();
				ds.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return output;
	}

	/**
	 * Insert data into a table. This should be used like the example below.
	 * <code><pre>
	db.insert(
		"EVENTS",
		new String[] { "sender",  "message" },
		new String[] { "me", "I like chicken, I like liver" });
	</pre></code>
	 * 
	 * @param table
	 *            Table to insert into
	 * @param fields
	 *            The fields to insert into {@code new String[] "field",
	 *            "field"}
	 * @param data
	 *            The data to insert {@code new string[] "data", "data"}
	 * @return A {@link Table} containing the data.
	 */
	// TODO support multiple row insert. new method with String[][] data + for
	// loop
	public long insert(String table, String[] fields, String[] data) {
		String sql = "INSERT INTO " + table + " (";
		for (String field : fields) {
			sql = sql.concat(" " + field + " ");
			if (fields[fields.length - 1] != field) {
				sql = sql.concat(", ");
			}
		}
		sql = sql.concat(") VALUES (");
		for (String datum : data) {
			sql = sql.concat(" '" + datum + "' ");
			if (data[data.length - 1] != datum) {
				sql = sql.concat(",");
			}
		}
		sql = sql.concat(")");
		try {
			log(sql);
			st = database.createStatement();
			st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			rs = st.getGeneratedKeys();
			if (rs != null) {
				rs.next();
				return rs.getLong(1);
			} else {
				err("Cannot get generated keys.");
			}
		} catch (SQLException e) {
			err(e.getMessage());
		}
		return Long.MAX_VALUE;
	}

	/**
	 * Insert a blob (typically a file) into the database.
	 * <code><pre>db.insertBlob(
					"Attachments",
					new String[] { "eid", "name", "*att" },
					new String[] { String.valueOf(id), "name",
							file.getAbsolutePath() });</pre></code>
	 * 
	 * @param table
	 *            the table to insert into
	 * @param fields
	 *            the fields to insert, blob fields should be marked with * as
	 *            the first char.
	 * @param objects
	 *            objects to insert
	 */
	public void insertBlob(String table, String[] fields, String[] objects) {
		String flds = "";
		String qs = "";
		for (String field : fields) {
			field = field.replace('*', ' ');
			field = field.trim();
			flds = flds.concat(field + " ");
			qs = qs.concat(" ?");
			String check = fields[fields.length - 1].replace('*', ' ').trim();
			if (!check.matches(field)) {
				flds = flds.concat(", ");
				qs = qs.concat(",");
			}
		}
		try {
			String sql = "INSERT INTO " + table + " (" + flds + ") VALUES("
					+ qs + ")";
			log(sql);
			ps = database.prepareStatement(sql);
			FileInputStream is = null;
			for (int i = 0; i < fields.length; i++) {
				if (fields[i].charAt(0) == '*') {
					File file = new File(objects[i]);
					is = new FileInputStream(file);
					ps.setBinaryStream(i + 1, is);
				} else {
					ps.setString(i + 1, objects[i]);
				}
			}
			ps.executeUpdate();
			if (is != null) {
				is.close();
			}
		} catch (SQLException e) {
			err(e.getMessage());
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			err(e.getMessage());
		} catch (IOException e) {
			err(e.getMessage());
		}
	}

	/**
	 * @return true if the database is closed or if an SQL exception occurs.
	 */
	public boolean isClosed() {
		try {
			return database.isClosed();
		} catch (SQLException e) {
			return true;
		}
	}

	public PreparedStatement prepare(String SQL) throws SQLException {
		return database.prepareStatement(SQL, ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
	}

	/**
	 * Select data from table.
	 * 
	 * @deprecated Confusing
	 * @param table
	 *            table to select from
	 * @param fields
	 *            fields to select
	 * @return a Table object with the data requested
	 */
	@Deprecated
	public ResultSet select(String table, String[] fields) {
		String sql = "SELECT ";
		for (String field : fields) {
			sql = sql.concat(field + " ");
			if (fields[fields.length - 1] != field) {
				sql = sql.concat(", ");
			}
		}

		sql = sql.concat(" FROM " + table.toUpperCase());
		return selectSQL(sql, fields);
	}

	/**
	 * Select data from the table with an additional clause.
	 * 
	 * @deprecated Confusing
	 * @param table
	 *            the table to select from
	 * @param fields
	 *            the fields to select
	 * @param append
	 *            append this to the SQL (WHERE eid=1, for example)
	 * @return a Table with the requested data
	 */
	@Deprecated
	public ResultSet select(String table, String[] fields, String append) {
		String sql = "SELECT ";
		for (String field : fields) {
			sql = sql.concat(field + " ");
			if (fields[fields.length - 1] != field) {
				sql = sql.concat(", ");
			}
		}

		sql = sql.concat(" FROM " + table.toUpperCase() + " \n");
		sql = sql.concat(append);
		return selectSQL(sql, fields);
	}

	/**
	 * Shuts down the connection to database.
	 */
	public void stop() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (st != null) {
				st.close();
			}
			database.close();
		} catch (SQLException e) {
			err(e.getMessage());
		}
	}

	/**
	 * Check to see if a table exists
	 * 
	 * @param table
	 *            the table to check
	 * @return true if table exists
	 */
	public boolean tableExists(String table) {
		try {
			return database.getMetaData()
					.getTables(null, null, table.toUpperCase(), null).next();
		} catch (SQLException e) {
			err(e.getMessage());
			return false;
		}
	}

	private void err(String err) {
		System.err.println(err);
	}

	private void log(String sql) {
		// System.out.println(sql);
	}

	private ResultSet selectSQL(String sql, String[] fields) {
		try {
			// Table<String> result = new Table<String>();
			Statement st = database
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			log(sql);
			rs = st.executeQuery(sql);
			// Vector<String> header = new Vector<String>();
			// //TODO This should grab from ResultSet to ensure accuracy.
			// for (String field : fields) {
			// header.add(field);
			// }
			// result.setHeaderRow(header);
			// while (rs.next()) {
			// Vector<String> row = new Vector<String>();
			// for (int j = 1; j <= fields.length; j++) {
			// row.add(rs.getString(j));
			// }
			// result.addRow(row);
			// }
			return rs;
		} catch (SQLException e) {
			err(e.getMessage());
		}
		return null;
	}
}
